package cz.zcu.sefler.mois.data;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Iterator;

import cz.zcu.sefler.mois.data.model.Monitor;
import cz.zcu.sefler.mois.data.model.Sentence;

/**
 * Trida MonitorDAO reprezentuje vrstvu aplikace pro pristup aplikacni logiky k
 * databazovemu ulozisti.
 * 
 * @author Vojtech Sefler
 * 
 */
public class MonitorDAO {

  /**
   * Adresa databaze.
   */
  String            dbUri;

  /**
   * Identifikator vsech vet.
   */
  private final int ALL_SENT_ID = -1;

  /**
   * Konstruktor, ktery vyrobi kompletni dbUri.
   * 
   * @param machine
   *          Adresa databaze.
   * @param db
   *          Jmeno databaze.
   * @param user
   *          Login do databaze.
   * @param pass
   *          Heslo do databaze.
   */
  public MonitorDAO(String machine, String db, String user, String pass) {
    this.dbUri = "jdbc:postgresql://" + machine + "/" + db + "?useUnicode=true&characterEncoding=utf-8&user=" + user + "&password=" + pass;
    try {
      Class.forName("org.postgresql.Driver");
    } catch (ClassNotFoundException e) {
    }
  }

  /**
   * Metoda ulozi/nahradi monitor do databaze. Zaroven ulozi i jednotlive vety
   * tohoto monitoru.
   * 
   * @param monitor
   *          Monitor, ktery chceme ulozit.
   */
  public void storeMonitor(Monitor monitor) throws SQLException {
    Connection con = DriverManager.getConnection(dbUri);
    Statement s = con.createStatement();

    /* Pokud se tento rozhovor v DB jiz nachazi, smazu ho. */
    String sql = "DELETE FROM op_interviews WHERE url='" + monitor.getUrl() + "';";
    s.executeUpdate(sql);

    /* Ulozeni noveho monitoru. */
    sql = "INSERT INTO op_interviews (url) VALUES ('" + monitor.getUrl() + "');";
    s.executeUpdate(sql);

    /* Zjistim si id, ktere monitor v databazi dostal. */
    sql = "SELECT currval('op_interviews_id_seq');";
    ResultSet rs = s.executeQuery(sql);
    while (rs.next()) {
      monitor.setId(rs.getInt("currval"));
    }

    s.close();
    con.close();

    /* Ulozeni vet monitoru, pokud nejake rozhovor obsahuje. */
    if (!monitor.getListSentences().isEmpty()) {
      this.storeSentences(monitor.getListSentences(), monitor.getId());
    }
  }

  /**
   * Metoda ulozi do databaze vety z listu a priradi jim cislo rozhovoru
   * monitorId.
   * 
   * @param listSentences
   *          Vety rozhovoru.
   * @param monitorId
   *          Id rozhovoru.
   */
  private void storeSentences(ArrayList<Sentence> listSentences, int monitorId) throws SQLException {
    Connection con = DriverManager.getConnection(dbUri);
    Statement s = con.createStatement();

    String sql = "INSERT INTO op_sentences (interview_id, type, content) VALUES ";
    Sentence sentence;
    for (Iterator<Sentence> iterator = listSentences.iterator(); iterator.hasNext();) {
      sentence = iterator.next();

      /* Nahrazeni znaku vety, ktere je nutno v SQL nahradit escape sekvenci. */
      sentence.setContent(sentence.getContent().replace("'", "\\'"));
      sentence.setContent(sentence.getContent().replace("`", "\\'"));

      sql += "(" + monitorId + ", '" + sentence.getType() + "', '" + sentence.getContent() + "')";

      /* Spravne ukonceni SQL prikazu. */
      if (iterator.hasNext()) {
        sql += ",";
      } else {
        sql += ";";
      }
    }
    s.executeUpdate(sql);

    s.close();
    con.close();
  }

  /**
   * Ziskani vsech rozhovoru vcetne jejich vet.
   * 
   * @return List rozhovoru i s vetami.
   */
  public ArrayList<Monitor> getMonitors() throws SQLException {
    /* Ziskam monitor bez vet (pouze id a url). */
    ArrayList<Monitor> monitorsNoSentences = getMonitorsNoSentences();

    ArrayList<Monitor> monitors = new ArrayList<Monitor>();

    /* Po jednom pridavam kompletni monitory do listu. */
    for (Monitor monitor : monitorsNoSentences) {
      monitors.add(this.getMonitor(monitor.getId()));
    }

    return monitors;
  }

  /**
   * Metoda vrati rozhovor dle identifikatoru i s jeho vetami.
   * 
   * @param monId
   *          Identifikator rozhovoru.
   * @return Objekt rozhovoru i s vetami.
   */

  public Monitor getMonitor(int monId) throws SQLException {
    Monitor newMonitor = new Monitor();
    Connection con = DriverManager.getConnection(dbUri);
    Statement s = con.createStatement();

    String sql = "SELECT id, url FROM op_interviews WHERE id='" + monId + "';";
    ResultSet rs = s.executeQuery(sql);

    while (rs.next()) {
      newMonitor.setId(rs.getInt("id"));
      newMonitor.setUrl(rs.getString("url"));
    }

    s.close();
    con.close();

    /* Nacteni vet tohoto rozhovoru. */
    newMonitor.setListSentences(this.getSentences(newMonitor.getId()));

    return newMonitor;
  }

  /**
   * Metoda ziska vsechny vety nezavisle na rozhovorech, ke kterym vety patri.
   * Pouzito pri vytvoreni prislusneho zip souboru.
   * 
   * @return Vsechny vety.
   */
  public ArrayList<Sentence> getAllSentences() throws SQLException {
    /* Vsechny vety znamenaji monitor s nesmyslnym identifikatorem. */
    ArrayList<Sentence> sn = this.getSentences(ALL_SENT_ID);

    return sn;
  }

  /**
   * Metoda vrati vsechny vety daneho rozhovoru. Pokud se jedna o rozhovor s
   * oznacenim ALL_SENT_ID, vratim vsechny vety. Rozhovory jsou cislovany od 1,
   * proto nebude existovat rozhovor s id rovno ALL_SENT_ID = -1.
   * 
   * @param monitorId
   *          Rozhovor, jehoz vety chci vratit.
   * @return List vet daneho rozhovoru.
   */
  private ArrayList<Sentence> getSentences(int monitorId) throws SQLException {
    ArrayList<Sentence> newList = new ArrayList<Sentence>();
    Connection con = DriverManager.getConnection(dbUri);
    Statement s = con.createStatement();

    String sql;
    if (monitorId == -1) {
      /* Vsechny vety. */
      sql = "SELECT id, type, content FROM op_sentences;";
    } else {
      /* Vety daneho rozhovoru. */
      sql = "SELECT id, type, content FROM op_sentences WHERE interview_id='" + monitorId + "';";
    }

    ResultSet rs = s.executeQuery(sql);
    while (rs.next()) {
      Sentence newSentence = new Sentence();
      newSentence.setId(rs.getInt("id"));
      newSentence.setType(rs.getString("type"));
      newSentence.setContent(rs.getString("content"));
      newList.add(newSentence);
    }

    s.close();
    con.close();

    Collections.sort(newList);
    return newList;
  }

  /**
   * Metoda vrati vsechny rozhovory bez jejich vet.
   * 
   * @return Rozhovory bez vet.
   */
  public ArrayList<Monitor> getMonitorsNoSentences() throws SQLException {
    ArrayList<Monitor> newMonitors = new ArrayList<Monitor>();
    Connection con = DriverManager.getConnection(dbUri);
    Statement s = con.createStatement();

    String sql = "SELECT id, url FROM op_interviews;";
    ResultSet rs = s.executeQuery(sql);
    while (rs.next()) {
      Monitor newMonitor = new Monitor();
      newMonitor.setId(rs.getInt("id"));
      newMonitor.setUrl(rs.getString("url"));
      newMonitors.add(newMonitor);
    }

    s.close();
    con.close();

    return newMonitors;
  }

}
